﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace VideoSystem
{
    public class OptionDb : IOptionDb
    {
        private string tableOptions = "Options";

        public int InsertOption(Option opt)
        {
            int result = -1;

            try
            {
                //Creating insert sql
                string sql = @"insert into Options values (" + opt.EpisodeId
                                                        + ",'" + opt.Option1Text
                                                        + "','" + opt.Option2Text + "')";
                SqlCommand cmd = DBConnection.GetDbCommand(sql);
                Console.WriteLine(sql);
                result = cmd.ExecuteNonQuery();
                DBConnection.CloseConnection();
            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("Sql error! in InsertOption");
                Console.WriteLine(sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Ordinary error! in InsertOption");
                Console.WriteLine(e.Message);
            }

            return result;
        }

        public string getVoteResult(int episodeId)
        {
            int vote1 = 0;
            int vote2 = 0;
            IDataReader dbReader;

            try
            {
                string sql = @"Select option1 from VotingResults where episodeId = " + episodeId + ";";
                SqlCommand cmd = DBConnection.GetDbCommand(sql);
                using (dbReader = cmd.ExecuteReader())
                {

                    if (dbReader.Read())
                    {
                        vote1 = (int)dbReader["option1"];
                        dbReader.Close();//line 60
                    }
                }

                string sql2 = @"Select option2 from VotingResults where episodeId = " + episodeId + ";";
                SqlCommand cmd2 = DBConnection.GetDbCommand(sql2);
                using (dbReader = cmd2.ExecuteReader())
                {

                    if (dbReader.Read())
                    {
                        vote2 = (int)dbReader["option2"];
                        dbReader.Close();
                    }
                }
                DBConnection.CloseConnection();


                string returnText = "Afstemning 1: " + vote1 + "\nAfstemning 2: " + vote2 + "";

                return returnText;
            }
            catch (Exception ex) {
                string fejl = "Fejl: "+ex+"";
                return fejl;
            }
        }

        public Option GetOptionsForId(int episodeId)
        {
            Option option = null;
            try
            {
                string sql = "SELECT * FROM " + tableOptions + " WHERE episodeId = " + episodeId;
                SqlCommand cmd = DBConnection.GetDbCommand(sql);
                SqlDataReader reader = cmd.ExecuteReader();
                

                while (reader.Read())
                {
                    string option1 = reader["option1"].ToString();
                    string option2 = reader["option2"].ToString();

                    option = new Option(episodeId, option1, option2);
                }
                reader.Close();
                DBConnection.CloseConnection();
                
            }
            catch (Exception sqlE)
            {
                Console.WriteLine("Sql error! in GetOptionsForId");
                Console.WriteLine(sqlE.Message);
            }
            return option;
        }
        
    }
}
